﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;
using RockCompany.Common.Lib;
using RockCompany.Master.DTO;
using RockCompany.Master.Constant;
using RockCompany.Common.Constant;
using System.Windows.Forms;

namespace RockCompany.Master.Model
{
    public class SI03BFormModel : CommonModel
    {
        #region Properties
        private DataTable _matCheck;
        public DataTable matCheck
        {
            get { return _matCheck; }
            set { _matCheck = value; }
        }

        private DataTable _matCheckDetails;
        public DataTable matCheckDetails
        {
            get { return _matCheckDetails; }
            set { _matCheckDetails = value; }
        } 
        #endregion

        #region Constructor
        public SI03BFormModel()
        {
            dbConnector = new DBConnector();
        } 
        #endregion

        #region Utility Methods
        public DataTable supplierLOV()
        {
            string sClause = "SELECT " + PHIEU_KN_HN_DTO.N_NHA_CUNG_UNG +
                             " FROM " + PHIEU_KN_HN_DTO.N_TABLE +
                             " GROUP BY " + PHIEU_KN_HN_DTO.N_NHA_CUNG_UNG;
            return selectData(sClause);
        }

        public DataTable importOrderLOV()
        {
            string sClause = "SELECT " + PHIEU_KN_HN_DTO.N_MS_DON_HANG +
                             " FROM " + PHIEU_KN_HN_DTO.N_TABLE +
                             " GROUP BY " + PHIEU_KN_HN_DTO.N_MS_DON_HANG;
            return selectData(sClause);
        }
        #endregion

        #region Main Methods
        public DataTable searchMatsCheckList(string sSup, string sOrder, string sfromDate, string sToDate)
        {
            string sSql = "SELECT *";
            sSql += " FROM " + PHIEU_KN_HN_DTO.N_TABLE;
            sSql += " WHERE ";
            if (!sSup.Equals(string.Empty))
                sSql += PHIEU_KN_HN_DTO.N_NHA_CUNG_UNG + " LIKE N'" + sSup + "' AND ";
            if (!sOrder.Equals(string.Empty))
                sSql += PHIEU_KN_HN_DTO.N_MS_DON_HANG + " LIKE '" + sOrder + "' AND ";
            sSql += "CONVERT(VARCHAR," + PHIEU_KN_HN_DTO.N_CREATION_TIME + ",112) BETWEEN " + sfromDate + " AND " + sToDate;
            matCheck = selectData(sSql);
            return matCheck;
        }

        public DataTable searchMatsCheckDetail(string matCheckNumber)
        {
            string sSql = "SELECT CONVERT(BIT,'0') AS CHON,";
            sSql += NGUYEN_VLIEU_VTU_DTO.N_TEN_NVL_VTU + ",";
            sSql += NGUYEN_VLIEU_VTU_DTO.N_QUY_CACH + ",";
            sSql += NGUYEN_VLIEU_VTU_DTO.N_DON_VI + ",";
            sSql += PHIEU_KN_HN_CT_DTO.N_TABLE + ".*";
            sSql += " FROM " + PHIEU_KN_HN_CT_DTO.N_TABLE + ",";
            sSql += NGUYEN_VLIEU_VTU_DTO.N_TABLE;
            sSql += " WHERE " + PHIEU_KN_HN_DTO.N_MS_PHIEU_KN + " = '" + matCheckNumber + "'";
            sSql += " AND " + PHIEU_KN_HN_CT_DTO.N_TABLE + "." + PHIEU_KN_HN_CT_DTO.N_MA_NVL_VTU + " = " + NGUYEN_VLIEU_VTU_DTO.N_TABLE + "." + NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU;
            matCheckDetails = selectData(sSql).Copy();
            return matCheckDetails;
        }

        public bool removeMatCheck(string sMatCheck)
        {
            string sClause = " WHERE " + PHIEU_KN_HN_DTO.N_MS_PHIEU_KN + " = '" + sMatCheck + "'";
            return removeRow(PHIEU_KN_HN_DTO.N_TABLE, sClause, null);
        }

        public bool removeMatCheckDetail(string sMatCheck)
        {
            string sClause = " WHERE " + PHIEU_KN_HN_DTO.N_MS_PHIEU_KN + " = '" + sMatCheck + "'";
            return removeRow(PHIEU_KN_HN_CT_DTO.N_TABLE, sClause, null);
        }

        public bool export2Excel(string newPath, PHIEU_KN_HN_DTO matCheck)
        {
            bool b = false;
            ExcelExporter excelExport = new ExcelExporter();
            excelExport.Trans = this.translator;
            if (excelExport.open(SI03BConst.EX_NAME))
            {
                if (!CommonModel.existData(matCheckDetails))
                    searchMatsCheckDetail(matCheck.MS_PHIEU_KN);

                excelExport.inputValue(SI03BConst.EX_POS_MS_PHIEU_KN, matCheck.MS_PHIEU_KN);
                excelExport.inputValue(SI03BConst.EX_POS_NHA_CU, matCheck.NHA_CUNG_UNG);
                excelExport.inputValue(SI03BConst.EX_POS_MS_DONDH, matCheck.MS_DON_HANG);
                excelExport.inputValue(SI03BConst.EX_POS_MS_HD, matCheck.MS_HOA_DON);
                excelExport.inputValue(SI03BConst.EX_POS_NGAY_NHAN_HANG, matCheck.NGAY_GIAO_HANG.ToShortDateString());

                List<DataColumn> disCol = new List<DataColumn>();
                disCol.Add(matCheckDetails.Columns[PHIEU_KN_HN_CT_DTO.N_MA_NVL_VTU]);
                disCol.Add(matCheckDetails.Columns[NGUYEN_VLIEU_VTU_DTO.N_QUY_CACH]);
                disCol.Add(matCheckDetails.Columns[NGUYEN_VLIEU_VTU_DTO.N_DON_VI]);
                disCol.Add(matCheckDetails.Columns[PHIEU_KN_HN_CT_DTO.N_SL_DAT_MUA]);
                disCol.Add(matCheckDetails.Columns[PHIEU_KN_HN_CT_DTO.N_SL_GIAO_HANG]);
                disCol.Add(matCheckDetails.Columns[PHIEU_KN_HN_CT_DTO.N_SL_THUC_NHAN]);
                disCol.Add(matCheckDetails.Columns[PHIEU_KN_HN_CT_DTO.N_GHI_CHU]);

                excelExport.inputRangeValues(matCheckDetails, disCol, SI03BConst.EX_BEGIN_GRID_COL, SI03BConst.EX_BEGIN_GRID_ROW, true);

                excelExport.inputValue(SI03BConst.EX_POS_NGUOI_LAP + Convert.ToString(matCheckDetails.Rows.Count + SI03BConst.EX_BEGIN_GRID_ROW + 1), SI03BConst.EX_VAL_NGUOI_LAP);
                b = excelExport.saveExcel(newPath);
                excelExport.close();
            }
            return b;
        }
        #endregion
    }
}
